﻿Imports Microsoft.Office.Interop.Excel
Imports System.IO
Imports System.Text.RegularExpressions
Public Class InputParser
    Implements IDisposable
    Private rules As Dictionary(Of String, Dictionary(Of Object, Integer))
    Private Xl As Application
    Private iWb As Workbook, oWB As Workbook
    Private iWs As Worksheet, oWs As Worksheet
    Private _date As Date
    Private _dateSet As Boolean
    Private cachepath As String
    Public Property err_global As List(Of String)
    Public Property succs_global_fromcodes As List(Of SuccObject)
    Private coeffs As SortedList(Of String, CoeffObject)
    'Dim matching As Boolean
    Sub Dispose() Implements IDisposable.Dispose
        Try
            kill()
        Catch
        End Try
    End Sub
    Sub parse(item As GridObject)
        prelude(item.path)
        CallByName(Me, "parse_" + item.type, CallType.Method) 'input parsing
        iWb.Close(SaveChanges:=False)

        If item.matchfile <> "" Then
            Dim tWb As Workbook = Xl.Workbooks.Open(item.matchfile, [ReadOnly]:=True)
            Dim tWs As Worksheet
            If tWb.Sheets.Count > 1 Then
                Try
                    tWs = tWb.Sheets.Item("хранилище кодов")
                Catch x As Exception
                    tWs = tWb.ActiveSheet
                End Try
            Else
                tWs = tWb.ActiveSheet
            End If



            Dim matcher As New MatchParser(Xl, oWs, tWs, item.type)
            'matcher.process()
            succs_global_fromcodes.AddRange(matcher.process())
            succs_global_fromcodes.AddRange(matcher.succ_fromcodes)
            err_global.AddRange(matcher.err)
            tWs = Nothing
            tWb.Close()
        End If
        save(item.type) ' saving input parsed
    End Sub
    Sub setTodayDate()
        _date = Date.Today
        _dateSet = True
    End Sub
    Sub parseDate(r As Integer, c As Integer)
        Try
            Dim d As String = Regex.Match(iWs.Cells(r, c).Value, "[0-9]{2}\.[0-9]{2}\.[0-9]{4}").ToString
            _date = Date.Parse(d)
            _dateSet = True
        Catch x As Exception
            _dateSet = False
        End Try
    End Sub
    Sub New(cp As String, Xl As Application)
        succs_global_fromcodes = New List(Of SuccObject)
        err_global = New List(Of String)
        Me.Xl = Xl
        cachepath = cp
        _dateSet = False




        coeffs = New SortedList(Of String, CoeffObject)
        For Each co As CoeffObject In My.Forms.Coefficients.CoeffsBindingSource
            coeffs.Add(co.type, co)
        Next

        Try
            Dim t As Dictionary(Of Object, Integer)
            rules = New Dictionary(Of String, Dictionary(Of Object, Integer))
            ' гранат
            t = New Dictionary(Of Object, Integer)
            t.Add(2, 2)
            t.Add(3, 4)
            t.Add(4, 5)
            t.Add(5, 3)
            t.Add(6, 6)
            t.Add(7, 8)
            t.Add(8, 7)

            t.Add(11, 10)

            't.Add(13, 11)

            t.Add("легковая", 16)
            t.Add(9, 9)
            t.Add("Гранат", 13)

            rules.Add("Гранат", t)
            'вакси_грузовая
            t = New Dictionary(Of Object, Integer)
            t.Add(1, 2)
            t.Add(6, 9)
            t.Add(16, 10)
            t.Add("Вакси_грузовая", 13)
            t.Add("грузовая", 16)
            rules.Add("Вакси_грузовая", t)

            'вакси_легковая
            t = New Dictionary(Of Object, Integer)
            t.Add(1, 2)
            t.Add(6, 9)
            t.Add(16, 10)
            t.Add(18, 8)
            t.Add("Вакси_легковая", 13)
            t.Add("легковая", 16)
            rules.Add("Вакси_легковая", t)

            'Вася
            t = New Dictionary(Of Object, Integer)
            t.Add(7, 2)
            t.Add(2, 3)
            t.Add(3, 5)
            t.Add(4, 6)
            t.Add(5, 8)
            t.Add(6, 10)
            t.Add("Вася", 13)
            t.Add("легковая", 16)
            rules.Add("Вася", t)

            'Кама-Кама
            t = New Dictionary(Of Object, Integer)
            t.Add(1, 2)
            t.Add(2, 3)
            t.Add(7, 10)
            t.Add("KAMA", 5)
            t.Add(8, 4)
            t.Add("Кама_Кама", 13)
            t.Add(9, 16)
            rules.Add("Кама_Кама", t)

            'Кама_Алтай
            t = New Dictionary(Of Object, Integer)
            t.Add(1, 3)
            t.Add(5, 2)
            t.Add(2, 9)
            t.Add(4, 10)
            t.Add("Алтайшина", 5)
            t.Add("Кама_Алтай", 13)
            t.Add(6, 16)
            rules.Add("Кама_Алтай", t)

            'Мотопарк
            t = New Dictionary(Of Object, Integer)
            t.Add(1, 2)
            t.Add(2, 5)
            t.Add(3, 3)
            t.Add(8, 8)
            t.Add(4, 4)
            t.Add(5, 9)
            't.Add(6, 10)
            t.Add(11, 10)
            t.Add(10, 1)
            t.Add(7, 6)

            't.Add(7, 8)
            't.Add(4, 4)
            't.Add(5, 9)
            't.Add(6, 10)
            't.Add(8, 1)
            't.Add(6, 6)
            t.Add("Мотопарк", 13)
            t.Add("легковая", 16)
            rules.Add("Мотопарк", t)

            'Олег
            t = New Dictionary(Of Object, Integer)
            t.Add(9, 2)
            t.Add(2, 3)
            t.Add(3, 5)
            t.Add(4, 6)
            t.Add(5, 8)
            t.Add(6, 10)
            t.Add(7, 9)
            t.Add(8, 1)
            t.Add("Олег", 13)
            t.Add("легковая", 16)
            rules.Add("Олег", t)

            'Опт
            t = New Dictionary(Of Object, Integer)
            t.Add(1, 1)
            t.Add(2, 3)
            t.Add(10, 2)
            t.Add(3, 5)
            t.Add(4, 9)
            t.Add(6, 10)
            t.Add(9, 8)
            t.Add("Опт", 13)
            t.Add(11, 16)
            rules.Add("Опт", t)

            'Римбат
            t = New Dictionary(Of Object, Integer)
            t.Add(1, 2)
            t.Add(2, 3)
            t.Add(3, 8)
            t.Add(4, 4)
            t.Add(5, 10)
            t.Add(7, 5)
            t.Add(8, 1)
            t.Add("Римбат", 13)
            t.Add(9, 16)
            rules.Add("Римбат", t)

            'СтартШина
            t = New Dictionary(Of Object, Integer)
            't.Add(12, 3)
            t.Add(2, 3)
            t.Add(13, 6)
            t.Add(1, 2)
            t.Add(10, 10)
            t.Add(8, 9)
            t.Add(3, 8)
            t.Add(6, 5)
            t.Add(11, 1)
            t.Add(7, 4)
            t.Add(14, 16)
            t.Add("СтартШина", 13)
            rules.Add("СтартШина", t)

            'Покрышкино
            t = New Dictionary(Of Object, Integer)
            t.Add(17, 1)
            t.Add(2, 2)
            t.Add(3, 3)
            t.Add(4, 4)
            t.Add(5, 5)
            t.Add(6, 6)
            t.Add(7, 8)
            t.Add(8, 9)
            t.Add(12, 10)
            t.Add("Покрышкино", 13)
            t.Add("легковая", 16)
            rules.Add("Покрышкино", t)

            'Шате-М
            t = New Dictionary(Of Object, Integer)
            t.Add(1, 1)
            t.Add(2, 8)
            t.Add(3, 5)
            t.Add(11, 10)
            t.Add(6, 9)
            t.Add(7, 2)
            t.Add(8, 3)
            t.Add(9, 4)
            t.Add(10, 6)
            t.Add("Шате-М", 13)
            t.Add("легковая", 16)
            rules.Add("Шате_М", t)

            'Белшинторг
            t = New Dictionary(Of Object, Integer)
            t.Add(2, 1)
            t.Add(8, 10)
            t.Add(5, 16)
            t.Add(6, 2)
            t.Add(7, 3)
            t.Add("Белшинторг", 13)
            rules.Add("Белшинторг", t)

            'Диски Опт
            t = New Dictionary(Of Object, Integer)
            t.Add(1, 1)
            t.Add(3, 2)
            t.Add(4, 10)
            t.Add(6, 12)
            t.Add(10, 3)
            t.Add(11, 4)
            t.Add(12, 5)
            t.Add(13, 6)
            t.Add(14, 7)
            t.Add(15, 8)
            t.Add(16, 9)
            t.Add("Диски_Опт", 16)
            rules.Add("Диски_Опт", t)

            'Диски Покрышкино
            t = New Dictionary(Of Object, Integer)
            t.Add(21, 1)
            t.Add(19, 4)
            t.Add(20, 5)
            t.Add(3, 2)
            t.Add(4, 3)
            t.Add(6, 6)
            t.Add(7, 7)
            t.Add(9, 10)
            t.Add(13, 12)
            t.Add(17, 13)
            'DIA
            t.Add(8, 8)
            'цвет
            t.Add("Диски_Покрышкино", 16)
            rules.Add("Диски_Покрышкино", t)

            'Диски_Гранат
            t = New Dictionary(Of Object, Integer)
            t.Add(14, 1)
            t.Add(1, 2)
            t.Add(3, 3)
            t.Add(4, 4)
            t.Add(5, 5)
            t.Add(6, 6)
            t.Add(7, 7)
            t.Add(8, 8)
            t.Add(9, 9)
            t.Add(10, 10)
            t.Add(13, 13)
            t.Add(12, 12)
            t.Add("Диски_Гранат", 16)
            rules.Add("Диски_Гранат", t)
        Catch x As ArgumentException
            MsgBox("Ошибка при иницализации соответствия колонок в типах прайсов", Title:=Variables.PROGFAULT)
            Environment.Exit(1)
        End Try
    End Sub
    Function parseCommon(type As String, Optional count As Integer = -1) As Integer
        Dim d As Dictionary(Of Object, Integer)
        d = rules.Item(type)
        Dim act
        With iWs
            If (count = -1) Then
                count = .Cells.CurrentRegion.Rows.Count
            End If
            If _dateSet = False Then
                setTodayDate()
            End If
            oWs.Range(oWs.Cells(1, 15), oWs.Cells(count, 15)).Value2 = _date.ToString("dd.MM.yyyy")
            _dateSet = False
            For Each act In d
                If TypeOf act.Key Is Integer Then ' i->j
                    .Range(.Cells(1, act.Key), .Cells(count, act.Key)).Copy()
                    oWs.Cells(1, act.Value).PasteSpecial(XlPasteType.xlPasteValues)
                ElseIf TypeOf act.Key Is Date Then

                Else ' "string"->j
                    oWs.Range(oWs.Cells(1, act.Value), oWs.Cells(count, act.Value)).Value2 = act.Key
                End If
            Next act
        End With

        'OHHH
        If Not Regex.IsMatch(type, "^Диски") Then

            

            placeCoeffs(type, count, 10, 11)
        Else
            '2 - В столбцах "ET, мм"(7 столбец) и "DIA, мм"(8 столбец) должны быть только цифры без "ЕТ" и "D"
            For Each col As Integer In {7, 8}
                Dim t As Object = getColumn(oWs, col, count).Value
                Dim t2(count) As String
                For i As Integer = 1 To count
                    If (t(i, 1) Is Nothing) Then
                        t2(i - 1) = ""
                    Else
                        t2(i - 1) = Regex.Replace(t(i, 1).ToString, "[a-zA-ZА-Яа-я ]", "")
                    End If

                Next
                getColumn(oWs, col, count).Value = Xl.WorksheetFunction.Transpose(t2)
            Next

            Dim cv As Object = oWs.Cells(1, 13).value
            If cv Is Nothing Then
                placeCoeffs(type, count, 12, 13)
            End If
            'ExcelOps.getColumn(oWs, 13).FormulaR1C1 = "=RC12*" + Variables.BYR_USD_CONV.ToString
            'ExcelOps.getColumn(oWs, 19).FormulaR1C1 = "=RC18*" + Variables.NDS_COEFF.ToString
            ExcelOps.getColumn(oWs, 19).FormulaR1C1 = "=RC12*" + DoubleToExcelString(NDS_COEFF) ' + "*" + Variables.BYR_USD_CONV.ToString
            ' ExcelOps.getColumn(oWs, 19).FormulaR1C1 = "=RC12*" + DoubleToExcelString(NDS_COEFF)

            'опт*4
            cv = oWs.Cells(1, 11).value
            If cv Is Nothing Then
                getColumn(oWs, 11, count).FormulaR1C1 = "=RC[1]*4"
            End If
            End If



            parseCommon = count
    End Function
    Sub placeCoeffs(type As String, count As Integer, get_col As Integer, place_col As Integer)



        Dim cf As CoeffObject = coeffs(type)
        With cf
            Dim i As Integer
            Dim price As Object = ExcelOps.getColumn(oWs, get_col, count, 1).Value
            Dim discount(count) As Double
            For i = 1 To count
                Dim td As Double
                If (Double.TryParse(price(i, 1), td)) Then
                    ' for rub
                    If td > 50000 Then
                        td = td / BYR_USD_CONV
                    End If

                    If (td <= .thr1) Then
                        td = td + .coeff
                    ElseIf td <= .thr2 Then
                        td = td * .coeff2
                    Else
                        td = td * .coeff3
                    End If
                    discount(i - 1) = td
                End If
            Next
            ExcelOps.getColumn(oWs, place_col, count, 1).Value = Xl.WorksheetFunction.Transpose(discount) ' discount
        End With
    End Sub
    Sub prelude(path As String)
        Try
            iWb = Xl.Workbooks.Open(path, ReadOnly:=True)
        Catch ex As Exception
            MsgBox("Ошибка при открытии файла " + path + ",дальнейшие действия приведут к ошибке работы.")
        End Try

        oWB = Xl.Workbooks.Add()
        Xl.Calculation = XlCalculation.xlCalculationManual
        oWs = oWB.ActiveSheet
        iWs = iWb.ActiveSheet
    End Sub
    Sub save(name As String)
        'oWs.UsedRange.ClearFormats()
        Dim temp As String = Path.GetTempFileName
        oWB.SaveAs(temp, FileFormat:=XlFileFormat.xlCSV)
        Xl.Calculation = XlCalculation.xlCalculationAutomatic
        oWB.Close()
        Try
            File.AppendAllLines(cachepath + name + ".csv", File.ReadAllLines(temp, Encoding1251), Encoding1251)
            File.Delete(temp)
        Catch ex As Exception
            MsgBox("Ошибка ввода-вывода при сохранении файла в кэш, тип " + name)
        End Try

    End Sub
    Sub parse_Вася()
        iWs.Cells.UnMerge()
        iWs.Columns(6).SpecialCells(XlCellType.xlCellTypeBlanks).EntireRow.Delete()
        Dim count As Integer = iWs.Columns(1).CurrentRegion.Rows.Count

        ExcelOps.splitColumnInTwo(Xl, iWs, 2, "^[^ ]* ", count, 7)

        parseCommon("Вася", count)
        ExcelOps.getColumn(oWs, 1, count).FormulaR1C1 = "=RC3"

    End Sub
    Sub parse_Вакси_грузовая()

        Dim head_del As Integer = iWs.Cells.Find(What:="Размер", _
                   SearchOrder:=XlSearchOrder.xlByRows, _
                   SearchDirection:=XlSearchDirection.xlNext).Row _
                   + 1 ' merged cells
        iWs.Rows("1:" + head_del.ToString).Delete(Shift:=XlDeleteShiftDirection.xlShiftUp)

        iWs.Cells.UnMerge()

        iWs.Columns(3).SpecialCells(XlCellType.xlCellTypeBlanks).EntireRow.Delete()
        iWs.Columns(6).SpecialCells(XlCellType.xlCellTypeBlanks).EntireRow.Delete()
        Dim rownumber As Integer = iWs.Cells(1, 2).currentregion.rows.count
        Dim src As Object = ExcelOps.getColumn(iWs, 3, rownumber).Value
        Dim model(rownumber) As String
        Dim vendor(rownumber) As String
        Dim code(rownumber) As String
        For i As Integer = 1 To rownumber
            Dim t As String = src(i, 1)
            code(i - 1) = Regex.Match(t, "^[^)]*").Value
            t = Regex.Replace(t, "^[^A-Za-z]*", "") 'kill everything before vendorname
            vendor(i - 1) = Regex.Match(t, "^[A-Za-z]+").ToString ' vendorname
            model(i - 1) = Regex.Replace(t, "^[A-Za-z]+ *", "") ' everything after vendorname
        Next
        ExcelOps.getColumn(oWs, 3, rownumber).Value = Xl.WorksheetFunction.Transpose(model)
        ExcelOps.getColumn(oWs, 5, rownumber).Value = Xl.WorksheetFunction.Transpose(vendor)
        ExcelOps.getColumn(oWs, 1, rownumber).Value = Xl.WorksheetFunction.Transpose(code)
        parseCommon("Вакси_грузовая", rownumber)

        ExcelOps.getColumn(oWs, 10).Value = ExcelOps.getColumn(iWs, 16).Value
        'ExcelOps.getColumn(iWs, 19, rownumber).FormulaR1C1 = "=RC16/" + BYR_USD_CONV.ToString
    End Sub
    Sub parse_Вакси_легковая()
        'ваксигрузокопипаста
        Dim head_del As Integer = iWs.Cells.Find(What:="Размер", _
                   SearchOrder:=XlSearchOrder.xlByRows, _
                   SearchDirection:=XlSearchDirection.xlNext).Row _
                   + 1 ' merged cells
        'Dim headrow As Range = iWs.Rows(head_del)
        iWs.Rows("1:" + head_del.ToString).Delete(Shift:=XlDeleteShiftDirection.xlShiftUp)
        'Dim col_remains As Integer = headrow.Find(What:="Минск", SearchOrder:=XlSearchOrder.xlByColumns, _
        '            SearchDirection:=XlSearchDirection.xlNext).Column ' остатки минские

        'Dim col_price_s As Range = headrow.Find(What:="Цена в", SearchOrder:=XlSearchOrder.xlByColumns, _
        '            SearchDirection:=XlSearchDirection.xlNext) ' минская цена
        'Dim col_price As Integer = headrow.FindNext(col_price_s).Column
        iWs.Cells.UnMerge()
        Dim rownumber As Integer = iWs.Cells.CurrentRegion.Rows.Count

        'a little bit new
        fillFromHorizontal(3, 18, rownumber)
        'end

        iWs.Columns(6).SpecialCells(XlCellType.xlCellTypeBlanks).EntireRow.Delete()
        rownumber = iWs.Cells(1, 1).currentregion.rows.count
        Dim src As Object = ExcelOps.getColumn(iWs, 3, rownumber).Value
        Dim model(rownumber) As String
        Dim vendor(rownumber) As String
        Dim code(rownumber) As String
        'ваксигрузокопипаста кончилась
        For i As Integer = 1 To rownumber
            Dim t As String = src(i, 1)
            'here goes dict
            For Each d As DictObject In My.Forms.Dictionary.dict
                If (Regex.IsMatch(t, d.source)) Then
                    t = Regex.Replace(t, d.source, d.replacement)
                    Exit For
                End If
            Next
            'then as usual
            code(i - 1) = Regex.Match(t, "^[^)]*").Value
            t = Regex.Replace(t, "^[^)]*\)? *", "")
            t = Regex.Replace(t, "^ ?[AА] ?/? ?[Шш]ин[аы]", "") ' AA - first latin,then cyrillic
            t = Regex.Replace(t, "^\W*", "") 'kill everything before vendorname
            vendor(i - 1) = Regex.Match(t, "^[\w-_]+").ToString ' vendorname
            model(i - 1) = Trim(Regex.Replace(t, "^[\w-_]+ *", "")) ' everything after vendorname
        Next
        ExcelOps.getColumn(oWs, 3, rownumber).Value = Xl.WorksheetFunction.Transpose(model)
        ExcelOps.getColumn(oWs, 5, rownumber).Value = Xl.WorksheetFunction.Transpose(vendor)
        ExcelOps.getColumn(oWs, 1, rownumber).Value = Xl.WorksheetFunction.Transpose(code)
        parseCommon("Вакси_легковая", rownumber)

        ExcelOps.getColumn(oWs, 10).Value = ExcelOps.getColumn(iWs, 16).Value
        'ExcelOps.getColumn(oWs, 19).FormulaR1C1 = "=RC16/" + BYR_USD_CONV.ToString
    End Sub
    Sub parse_Гранат()
        iWs = iWb.Sheets("2. Шины Лето-Зима")

        iWs.Rows("1:2").Delete(Shift:=XlDeleteShiftDirection.xlShiftUp)
        Dim count As Integer = parseCommon("Гранат")
        ExcelOps.getColumn(oWs, 1, count).FormulaR1C1 = "=CONCATENATE(RC2,RC4,RC5,RC3)"
        ExcelOps.getColumn(oWs, 11, count).Value = ExcelOps.getColumn(iWs, 13).Value


    End Sub
    Sub parse_Кама_Алтай()
        Dim i As Integer
        iWs.Rows(1).delete(Shift:=XlDeleteShiftDirection.xlShiftUp)

        Dim count As Integer = iWs.Cells.CurrentRegion.Rows.Count
        fillFromHorizontal(3, 6, count)

        Dim other As Integer = iWs.Columns.Find("Другое", SearchOrder:=XlSearchOrder.xlByRows).Row
        ExcelOps.getColumn(iWs, 1, count, other).EntireRow.Delete(XlDeleteShiftDirection.xlShiftUp)


        iWs.Columns(3).SpecialCells(XlCellType.xlCellTypeBlanks).EntireRow.Delete(Shift:=XlDeleteShiftDirection.xlShiftUp)
        count = iWs.Cells.CurrentRegion.Rows.Count
        Dim srcmodel As Object = ExcelOps.getColumn(iWs, 1, count).Value
        Dim tire_src As Object = ExcelOps.getColumn(iWs, 6, count).Value
        Dim sizes(count) As String
        Dim actual_model(count) As String
        Dim tire_res(count) As String
        For i = 1 To count - 1
            Dim t As String = srcmodel(i, 1)
            t = Regex.Replace(t, "/ *", "/")
            sizes(i - 1) = Trim(Regex.Match(t, "^[^ ]* ").Value)
            actual_model(i - 1) = Trim(Regex.Replace(t, "^[^ ]* ", ""))

            t = tire_src(i, 1)
            Select Case t
                Case "Грузовые", "Сельскохозяйственные, погрузчики"
                    tire_res(i - 1) = "грузовая"
                Case Else
                    tire_res(i - 1) = "легковая"
            End Select
        Next
        ExcelOps.getColumn(iWs, 5, count).Value = Xl.WorksheetFunction.Transpose(sizes)
        ExcelOps.getColumn(iWs, 1, count).Value = Xl.WorksheetFunction.Transpose(actual_model)
        ExcelOps.getColumn(iWs, 6, count).Value = Xl.WorksheetFunction.Transpose(tire_res)
        ExcelOps.getColumn(iWs, 4, count).FormulaR1C1 = "=RC3/" + BYR_USD_CONV.ToString
        parseCommon("Кама_Алтай", count)
        ExcelOps.getColumn(oWs, 1, count).FormulaR1C1 = "=CONCATENATE(RC2,RC3)"

    End Sub
    Sub parse_Кама_Кама()

        parseDate(1, 2)

        iWs.Cells.UnMerge()
        iWs.Rows("1:3").delete(Shift:=XlDeleteShiftDirection.xlShiftUp)
        iWs.Columns(1).delete(Shift:=XlDeleteShiftDirection.xlShiftToLeft)


        Dim count As Integer = iWs.Cells(1, 1).CurrentRegion.Rows.Count
        fillFromHorizontal(5, 9, count)
        Dim tire_orig As Object = ExcelOps.getColumn(iWs, 9, count).Value
        Dim tire_res(count) As String
        For i As Integer = 1 To count
            Dim t As String = tire_orig(i, 1)
            Select Case t
                Case "Шины для грузовых автомобилей", "Шины сельскохозяйственные", "Шины для погрузчиков", "Шины цельнометаллокордные KAMA"
                    tire_res(i - 1) = "грузовая"
                Case Else
                    tire_res(i - 1) = "легковая"
            End Select
        Next
        ExcelOps.getColumn(iWs, 9, count).Value = Xl.WorksheetFunction.Transpose(tire_res)


        iWs.Columns(5).SpecialCells(XlCellType.xlCellTypeBlanks).EntireRow.Delete(Shift:=XlDeleteShiftDirection.xlShiftUp)
        count = iWs.Cells(1, 1).CurrentRegion.Rows.Count
        iWs.Columns(7).formulaR1C1 = "=RC5/" + BYR_USD_CONV.ToString
        iWs.Columns(8).formulaR1C1 = "=CONCATENATE(RC3,RC4)"
        parseCommon("Кама_Кама", count)
        ExcelOps.getColumn(oWs, 1, count).FormulaR1C1 = "=CONCATENATE(RC2,RC3,RC4)"

    End Sub
    Sub parse_Мотопарк()

        Dim tWb As Workbook = Xl.Workbooks.Add
        Dim tws As Worksheet = tWb.ActiveSheet
        Dim curline As Integer = 1
        For Each ws As Worksheet In iWb.Worksheets
            'удадение
            'mWs.Rows("1:" + rowsdel.ToString).Delete(Shift:=XlDeleteShiftDirection.xlShiftUp)
            Try
                ws.Columns(7).SpecialCells(XlCellType.xlCellTypeBlanks).EntireRow.Delete(Shift:=XlDeleteShiftDirection.xlShiftUp)
            Catch
            End Try

            Try
                Dim headers As Range = ws.Columns(1).Find("Размер", SearchOrder:=XlSearchOrder.xlByRows, MatchCase:=False)
                Try
                    headers.Find("Опт 1", SearchOrder:=XlSearchOrder.xlByColumns, MatchCase:=False).EntireColumn.Delete(Shift:=XlDeleteShiftDirection.xlShiftToLeft)
                Catch
                    If Not (ws.Cells(1, 9).Value Is Nothing) Then
                        'fallback, October 2014 price, headers get deleted by ws.Columns(7).special... above
                        ws.Columns(6).EntireColumn.Delete(Shift:=XlDeleteShiftDirection.xlShiftToLeft)
                    End If
                End Try
                headers.EntireRow.Delete(Shift:=XlDeleteShiftDirection.xlShiftUp)
                ws.Columns(1).Find("Размер", SearchOrder:=XlSearchOrder.xlByRows).EntireRow.Delete(Shift:=XlDeleteShiftDirection.xlShiftUp)
            Catch x As NullReferenceException
            End Try

            Try
                ws.Columns(1).SpecialCells(XlCellType.xlCellTypeBlanks).EntireRow.Delete(Shift:=XlDeleteShiftDirection.xlShiftUp)
            Catch
            End Try

            Dim count As Integer = ws.Columns(1).CurrentRegion.Rows.Count
            ExcelOps.getColumn(ws, 8, count).Value = ws.Name

            ws.Cells.CurrentRegion.Copy()
            tws.Cells(curline, 1).PasteSpecial(XlPasteType.xlPasteValues)
            curline = curline + count
        Next
        iWs = Nothing
        iWb.Close()
        iWb = tWb
        iWs = tws
        tws = Nothing
        tWb = Nothing
        ExcelOps.getColumn(iWs, 10, curline - 1).FormulaR1C1 = "=CONCATENATE(RC1,RC3,RC4)"
        ExcelOps.getColumn(iWs, 11, curline - 1).FormulaR1C1 = "=RC6/" + BYR_USD_CONV.ToString
        parseCommon("Мотопарк", curline - 1)

    End Sub
    Sub parse_Олег()

        iWs.Cells.UnMerge()
        iWs.Columns(6).SpecialCells(XlCellType.xlCellTypeBlanks).EntireRow.Delete(Shift:=XlDeleteShiftDirection.xlShiftUp)
        Dim count As Integer = iWs.Columns(1).CurrentRegion.rows.Count
        Dim src As Object = ExcelOps.getColumn(iWs, 5, count).Value ' в столбце с сезоном мелькают надписи вида "1шт", достанем
        Dim season_replaced(count) As String
        Dim size(count) As String
        Dim tirecount(count) As Object ' not integer, because if not set defaults to 0

        For i As Integer = 1 To count
            Dim t As String = src(i, 1)
            Dim m As Match = Regex.Match(t, "[0-9]+")
            If (m.Success) Then
                tirecount(i - 1) = Convert.ToInt32(m.Value)
            End If
            season_replaced(i - 1) = Regex.Replace(t, " +[0-9]+ ?шт", "")
        Next


        ExcelOps.splitColumnInTwo(Xl, iWs, 2, "^[^ ]* ", count, 9)

        ExcelOps.getColumn(iWs, 5, count).Value = Xl.WorksheetFunction.Transpose(season_replaced)
        ExcelOps.getColumn(iWs, 7, count).Value = Xl.WorksheetFunction.Transpose(tirecount)

        ExcelOps.getColumn(iWs, 8, count).FormulaR1C1 = "=RC2"
        parseCommon("Олег", count)

    End Sub
    Sub parse_Опт()


        iWs.Rows("1:5").Delete(Shift:=XlDeleteShiftDirection.xlShiftUp)
        Dim count As Integer = iWs.Columns(1).CurrentRegion.rows.Count
        Dim src As Object = ExcelOps.getColumn(iWs, 9, count).Value ' в столбце с сезоном есть "диски, передние, задние, прицепные", нас интересует только сезон, очищаем неугодные
        Dim res(count) As String
        Dim tiretype(count) As String
        For i As Integer = 1 To count
            Dim t As String = LCase(src(i, 1))
            If t = "передние" Or t = "задние" Or t = "прицепные" Then
                tiretype(i - 1) = "грузовая"
            Else
                tiretype(i - 1) = "легковая"
            End If
            Select Case t
                Case "зима", "лето", "всесезонные", "передние", "задние", "прицепные"
                    res(i - 1) = t
                Case "диски"
                    res(i - 1) = ""
                Case Else
                    res(i - 1) = "-"
            End Select
        Next
        ExcelOps.getColumn(iWs, 9, count).Value = Xl.WorksheetFunction.Transpose(res)
        ExcelOps.getColumn(iWs, 11, count).Value = Xl.WorksheetFunction.Transpose(tiretype)
        ExcelOps.getColumn(iWs, 9, count).SpecialCells(XlCellType.xlCellTypeBlanks).EntireRow.Delete(Shift:=XlDeleteShiftDirection.xlShiftUp)
        count = iWs.Columns(1).CurrentRegion.rows.Count

        ExcelOps.splitColumnInTwo(Xl, iWs, 2, "^[^ ]* ", count, 10, 2)

        parseCommon("Опт", count)

    End Sub
    Sub parse_Покрышкино()

        iWs.Cells.UnMerge()
        Dim c As Range = iWs.Columns(2).Find("Типоразмер", SearchOrder:=XlSearchOrder.xlByRows)
        iWs.Rows("1:" + (c.Row + 2).ToString).delete()
        iWs.Columns(3).SpecialCells(XlCellType.xlCellTypeBlanks).EntireRow.Delete(XlDeleteShiftDirection.xlShiftUp)
        Dim count As Integer = iWs.Cells(1, 1).currentregion.rows.count
        ExcelOps.getColumn(iWs, 3, count).Replace(What:="""АКЦИЯ""", Replacement:="", LookAt:=XlLookAt.xlPart, MatchCase:=False)
        'ExcelOps.getColumn(iWs, 18, count).FormulaR1C1 = "=RC16/" + BYR_USD_CONV.ToString
        ExcelOps.getColumn(iWs, 17, count).FormulaR1C1 = "=CONCATENATE(RC2,RC3,RC4,RC5)"
        parseCommon("Покрышкино")

    End Sub
    Sub parse_Римбат()
        iWs = iWb.Sheets("Шины")
        'Dim currency_rimbat As Double = iWs.Cells(6, 5).Value()
        Dim d As Range = iWs.Cells.Find("размер", SearchOrder:=XlSearchOrder.xlByRows)

        parseDate(1, 1)
        iWs.Rows("1:" + d.Row.ToString).Delete()
        Dim count As Integer = iWs.Cells.CurrentRegion.Rows.Count

        iWs.Cells.UnMerge()
        fillFromHorizontal(6, 7, count)
        fillFromHorizontal(6, 9, count, 2)

        Dim tire_orig As Object = ExcelOps.getColumn(iWs, 9, count).Value
        Dim tire_res(count) As String
        For i As Integer = 1 To count
            Dim t As String = tire_orig(i, 1)
            Select Case t
                Case "Грузовые шины", "Сельскохозяйсвенные шины", "Сельскохозяйственные шины"
                    tire_res(i - 1) = "грузовая"
                Case Else
                    tire_res(i - 1) = "легковая"
            End Select
        Next
        ExcelOps.getColumn(iWs, 9, count).Value = Xl.WorksheetFunction.Transpose(tire_res)



        ExcelOps.getColumn(iWs, 6, count).SpecialCells(XlCellType.xlCellTypeBlanks).EntireRow.Delete(Shift:=XlDeleteShiftDirection.xlShiftUp)
        count = iWs.Cells.CurrentRegion.Rows.Count 'recount rows
        ExcelOps.getColumn(iWs, 5, count).FormulaR1C1 = "=RC6/" + BYR_USD_CONV.ToString 'currency_rimbat.ToString
        ExcelOps.getColumn(iWs, 8, count).FormulaR1C1 = "=CONCATENATE(RC1,RC2)"
        parseCommon("Римбат", count)

    End Sub
    Sub parse_СтартШина()
        'MsgBox("Обработка прайса 'СтартШина' недоработана")
        iWs.Rows("1:8").Delete(Shift:=XlDeleteShiftDirection.xlShiftUp)

        Dim count As Integer
        iWs.Cells.UnMerge()

        Dim cameras As Range = iWs.Columns(1).Find("Камеры", SearchOrder:=XlSearchOrder.xlByRows)
        count = cameras.CurrentRegion.Rows.Count
        ExcelOps.getColumn(iWs, 1, count, cameras.Row).EntireRow.Delete(Shift:=XlDeleteShiftDirection.xlShiftUp)
        count = iWs.Cells(1, 1).CurrentRegion.Rows.Count
        fillFromHorizontal(7, 14, count)

        Dim tire_orig As Object = ExcelOps.getColumn(iWs, 14, count).Value
        Dim tire_res(count) As String
        For i As Integer = 1 To count
            Dim t As String = tire_orig(i, 1)
            Select Case UCase(Trim(t))
                Case "ШИНЫ ДЛЯ ТРАКТОРОВ И С/Х МАШИН", "ШИНЫ ДЛЯ ПОГРУЗЧИКОВ", "ШИНЫ ДЛЯ ГРУЗОВЫХ АВТОМОБИЛЕЙ И СПЕЦТЕХНИКИ"
                    tire_res(i - 1) = "грузовая"
                Case Else
                    tire_res(i - 1) = "легковая"
            End Select
        Next
        ExcelOps.getColumn(iWs, 14, count).Value = Xl.WorksheetFunction.Transpose(tire_res)


        iWs.Columns(9).SpecialCells(XlCellType.xlCellTypeBlanks).EntireRow.Delete(Shift:=XlDeleteShiftDirection.xlShiftUp)
        count = iWs.Columns(9).currentregion.rows.count



        ExcelOps.getColumn(iWs, 10, count).FormulaR1C1 = "=RC9/" + BYR_USD_CONV.ToString
        ExcelOps.getColumn(iWs, 11, count).FormulaR1C1 = "=CONCATENATE(RC1,RC2,RC4)"
        'ExcelOps.getColumn(iWs, 12, count).FormulaR1C1 = "=CONCATENATE(RC2,RC4,RC5)"
        ExcelOps.getColumn(iWs, 13, count).FormulaR1C1 = "=CONCATENATE(RC4,RC5)"

        parseCommon("СтартШина", count)

    End Sub
    Sub parse_Шате_М()
        ' Наименование прайса - Шате - М
        '135/80R13 Polaris3 70T Страна производства: Чехия

        '135/80R13 - размер
        'Polaris3 -модель
        '70T -индексы

        'Все идет по порядку
        'Код столбец А
        iWs.Rows("1:3").Delete(XlDeleteShiftDirection.xlShiftUp)
        Dim count As Integer = iWs.Cells(1, 1).CurrentRegion.Rows.count
        Dim col As Object = ExcelOps.getColumn(iWs, 1).Value
        Dim country(count) As String
        Dim size(count) As String
        Dim model(count) As String
        Dim index(count) As String
        'split 1 col
        For i As Integer = 0 To count - 1
            Dim t As String = col(i + 1, 1)
            Dim split1() As String = Regex.Split(t, " *Страна *производства *: *")
            If (split1.Length > 1) Then
                country(i) = split1(1)
            End If
            Dim split2() As String = Regex.Split(split1(0), " +")
            If (split2.Length = 1) Then ' камеры?
                model(i) = split2(0)
            Else
                Dim mc As MatchCollection = Regex.Matches(split1(0), "[0-9]+[A-Z]")
                index(i) = mc.Item(mc.Count - 1).Value
                'split2 = Regex.Split(split1(0).Replace(index(i), ""), " +") 'redo split2
                Dim rem3 As String = split1(0).Replace(index(i), "")
                size(i) = RTrim(Regex.Match(rem3, "^[^ ]+ ").Value)
                model(i) = Trim(rem3.Replace(size(i), ""))
            End If

        Next
        'replace Шина зимняя with зимняя
        getColumn(iWs, 2, count).Replace("Шина ", "")

        getColumn(iWs, 7, count).Value = Xl.WorksheetFunction.Transpose(size)
        getColumn(iWs, 8, count).Value = Xl.WorksheetFunction.Transpose(model)
        getColumn(iWs, 9, count).Value = Xl.WorksheetFunction.Transpose(index)
        getColumn(iWs, 10, count).Value = Xl.WorksheetFunction.Transpose(country)
        getColumn(iWs, 11, count).FormulaR1C1 = "=IF(ISBLANK(RC5),RC4/" + BYR_USD_CONV.ToString + ",RC4*0.69/" + BYR_USD_CONV.ToString + ")"
        parseCommon("Шате_М")
    End Sub
    Sub parse_Белшинторг()
        Dim header As Range = iWs.Cells.Find("Товар", MatchCase:=False)
        iWs.Rows("1:" + (header.Row + 1).ToString).Delete(Shift:=XlDeleteShiftDirection.xlShiftUp)
        Dim count As Integer = iWs.Cells(1, 2).CurrentRegion.Rows.Count
        fillFromHorizontal(empty_col:=4, col_to_set:=5, count:=count, meaning_col:=2)
        ExcelOps.getColumn(iWs, 4, count).SpecialCells(XlCellType.xlCellTypeBlanks).EntireRow.Delete(Shift:=XlDeleteShiftDirection.xlShiftUp)
        count = iWs.Cells(1, 2).CurrentRegion.Rows.Count
        Dim tire_orig As Object = ExcelOps.getColumn(iWs, 5, count).Value
        Dim tire_res(count) As String
        For i As Integer = 1 To count
            Dim t As String = tire_orig(i, 1).ToString.ToLower
            Select Case t
                Case "легковые", "легкогрузовые"
                    tire_res(i - 1) = "легковая"
                Case "акб"
                    tire_res(i - 1) = ""
                Case Else
                    tire_res(i - 1) = "грузовая"
            End Select
        Next
        ExcelOps.getColumn(iWs, 5, count).Value = Xl.WorksheetFunction.Transpose(tire_res)
        ExcelOps.getColumn(iWs, 5, count).SpecialCells(XlCellType.xlCellTypeBlanks).EntireRow.Delete(Shift:=XlDeleteShiftDirection.xlShiftUp) ' акб cleanup
        count = iWs.Cells(1, 2).CurrentRegion.Rows.Count
        splitColumnInTwo(Xl, iWs, 2, "^[^ ]* ", count, 6, 7)
        getColumn(iWs, 8, count).FormulaR1C1 = "=RC4/" + BYR_USD_CONV.ToString
        parseCommon("Белшинторг", count)
    End Sub
    Sub parse_Диски_Опт()
        parseDate(2, 2)
        iWs.Rows("1:5").Delete(XlDeleteShiftDirection.xlShiftUp)
        Dim count As Integer = iWs.Cells(1, 1).CurrentRegion.Rows.count
        Dim orig_season As Object = getColumn(iWs, 9, count).Value
        Dim disc(count) As String
        For i As Integer = 1 To count
            If LCase(orig_season(i, 1)) <> "диски" Then
                disc(i - 1) = Nothing
            Else
                disc(i - 1) = orig_season(i, 1)
            End If
        Next

        getColumn(iWs, 9, count).Value = Xl.WorksheetFunction.Transpose(disc)
        getColumn(iWs, 9, count).SpecialCells(XlCellType.xlCellTypeBlanks).EntireRow.Delete(XlDeleteShiftDirection.xlShiftUp)
        count = iWs.Cells(1, 1).CurrentRegion.Rows.count

        Dim src As Object = getColumn(iWs, 2, count).Value
        Dim model(count) As String
        Dim j_(count) As String
        Dim diameter(count) As String
        Dim PCD(count) As String
        Dim ET(count) As String
        Dim DIA(count) As String
        Dim color(count) As String
        For i As Integer = 1 To count
            Dim t As String = src(i, 1)
            Try
                Dim spl() As String = Regex.Split(t, " +")
                model(i - 1) = spl(0)

                spl(1) = Regex.Replace(spl(1), "х", "x") 'yeah

                Dim tempspl() As String = Regex.Split(spl(1), "x")
                j_(i - 1) = tempspl(0)
                diameter(i - 1) = tempspl(1)
                PCD(i - 1) = spl(2)
                ET(i - 1) = spl(3)
                DIA(i - 1) = spl(4)

                color(i - 1) = ""
                For j As Integer = 5 To spl.Length - 1
                    color(i - 1) = color(i - 1) + " " + spl(j)
                Next
            Catch x As IndexOutOfRangeException
                model(i - 1) = t
                j_(i - 1) = ""
                diameter(i - 1) = ""
                PCD(i - 1) = ""
                ET(i - 1) = ""
                DIA(i - 1) = ""
                color(i - 1) = ""
            End Try
        Next

        getColumn(iWs, 10, count).Value = Xl.WorksheetFunction.Transpose(model)
        getColumn(iWs, 11, count).Value = Xl.WorksheetFunction.Transpose(diameter)
        getColumn(iWs, 12, count).Value = Xl.WorksheetFunction.Transpose(j_)
        getColumn(iWs, 13, count).Value = Xl.WorksheetFunction.Transpose(PCD)
        getColumn(iWs, 14, count).Value = Xl.WorksheetFunction.Transpose(ET)
        getColumn(iWs, 15, count).Value = Xl.WorksheetFunction.Transpose(DIA)
        getColumn(iWs, 16, count).Value = Xl.WorksheetFunction.Transpose(color)

        parseCommon("Диски_Опт")
        'F  7x16 5x108 ET43 D70.1 HIGH GLOSS,, где
        'F -модель
        '7 - J"
        '16 - диаметр
        '5x108(-PCD, мм)
        'ET43(-ET, мм)
        'D70(0.1 - DIA, мм)
        'HIGH GLOSS - цвет
    End Sub
    Sub parse_Диски_Гранат()
        'iWs = iWb.Sheets("1. Диски Литые")
        ' iWs = iWb.Worksheets.
        Dim find As String
        For Each ws As Worksheet In iWb.Worksheets
            'find = ws.Name.Trim("Диски Литые")
            find = ws.Name.EndsWith("Диски Литые")
            'ws.Name.EndsWith()

            If find Then
                iWs = ws
                Exit For
            End If
        Next
        iWs.Rows("1:2").Delete(XlDeleteShiftDirection.xlShiftUp)
        getColumn(iWs, 14).FormulaR1C1 = "=CONCATENATE(RC1,RC3,RC4,RC5,RC6,RC7,RC8)"
        parseCommon("Диски_Гранат")
    End Sub
    Sub parse_Диски_Покрышкино()
        'imported from Покрышкино
        iWs.Cells.UnMerge()
        Dim c As Range = iWs.Columns(2).Find("Типоразмер", SearchOrder:=XlSearchOrder.xlByRows)
        iWs.Rows("1:" + (c.Row + 2).ToString).delete()

        Dim count As Integer = iWs.Cells(1, 2).CurrentRegion.Rows.count
        'fillFromHorizontal(3, 14, count, 5)
        iWs.Columns(1).SpecialCells(XlCellType.xlCellTypeBlanks).EntireRow.Delete(XlDeleteShiftDirection.xlShiftUp)
        count = iWs.Cells(1, 1).CurrentRegion.Rows.count
        Dim src As Object = getColumn(iWs, 2, count).Value 'типоразмер
        Dim j_(count) As String
        Dim diameter(count) As String
        For i As Integer = 1 To count
            src(i, 1) = Regex.Replace(src(i, 1), "х", "x")
            Dim spl() As String = Regex.Split(src(i, 1), "x")
            If spl.Length > 1 Then
                j_(i - 1) = spl(0)
                diameter(i - 1) = spl(1)
            Else
                diameter(i - 1) = spl(0)
                j_(i - 1) = ""
            End If
        Next

        getColumn(iWs, 19, count).Value = Xl.WorksheetFunction.Transpose(diameter)
        getColumn(iWs, 20, count).Value = Xl.WorksheetFunction.Transpose(j_)

        getColumn(iWs, 21).FormulaR1C1 = "=CONCATENATE(RC2,RC4,RC5,RC6,RC7,RC8)"
        parseCommon("Диски_Покрышкино")
    End Sub
    Sub kill() 'As List(Of SuccObject) ' there are some IDisposable and such
        oWB = Nothing
        iWb = Nothing
        'Xl.Quit()
        Xl = Nothing
        ' kill = succs_global
    End Sub
    Sub fillFromHorizontal(empty_col As Integer, col_to_set As Integer, count As Integer, Optional meaning_col As Integer = 1)
        Dim horizontal As New List(Of Integer)
        Dim t As Range = iWs.Columns(empty_col).Find("", SearchOrder:=XlSearchOrder.xlByRows)
        horizontal.Add(1)
        While t.Row < count
            horizontal.Add(t.Row)
            t = iWs.Columns(empty_col).FindNext(t)
        End While
        'iWs.Columns(7).Clear()
        For i As Integer = 0 To horizontal.Count - 2 ' setting vendors
            iWs.Range(iWs.Cells(horizontal(i), col_to_set), iWs.Cells(horizontal(i + 1) - 1, col_to_set)).Value = iWs.Cells(horizontal(i), meaning_col).Value
        Next
        iWs.Range(iWs.Cells(horizontal(horizontal.Count - 1), col_to_set), iWs.Cells(count, col_to_set)).Value = iWs.Cells(horizontal(horizontal.Count - 1), meaning_col).Value
    End Sub


End Class
